/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package todotask.Database;

import java.beans.PropertyChangeListener;
import java.beans.PropertyChangeSupport;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFrame;
import javax.swing.JOptionPane;

public class Database {

    public static Integer numberOfSentences = Integer.valueOf(0);
    private PropertyChangeSupport propertyChangeSupport = new PropertyChangeSupport(this);
    protected Object obj;
    public static final String PROP_OBJ = "obj";
    private Connection conn;
    private String pathDbProperties = "db.properties";

    public static void main(String args[]) {
        try {
            new Database();
        } catch (InstantiationException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Database()
            throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        HashMap map = getProperties(pathDbProperties);
        String user = map.get("user").toString();
        String pass = map.get("password").toString();
        String host = map.get("host").toString();
        String schema = map.get("schema").toString();

        String cadenaCon = "jdbc:mysql://" + host + ":3306/" + schema + "?user=" + user + "&password=" + pass;

//        JOptionPane.showMessageDialog(new JFrame(), cadenaCon);

        Database(cadenaCon, user, pass);
    }

    /**
     * METODO QUE DEVUELVE UN OBJETO HASHMAP CON LOS VALORES
     * DEL FICHERO .PROPERTIES
     *
     * @param name, string con el path y el nombre del fichero .properties a leer
     * @return HashMap con los valores, de lo contrario devolvemos null
     */
    private HashMap getProperties(String name) {
        /* Atributos necesarios */
        FileInputStream file = null;

        /* Abrimos el fichero properties para leer de el */
        try {
            file = new FileInputStream(name);

            /* Instanciamos el objeto Properties */
            Properties propers = new Properties();
            try {
                propers.load(file);
            } catch (IOException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    /* Cerramos el fichero desde de leerlo */
                    file.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }

            /* Return */
            if (!propers.isEmpty()) {
                return new HashMap(propers);
            }

        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
            return null;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        } catch (NullPointerException nulo) {
            nulo.printStackTrace();
            return null;
        } catch (Exception excep) {
            excep.printStackTrace();
            return null;
        }

        return null;
    }

    public final void Database(String conexion, String user, String pass) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        this.conn = DriverManager.getConnection(conexion, user, pass);
    }

    public boolean isAlive() {
        try {
            return this.conn.isValid(0);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public void cerrar() throws SQLException {
        this.conn.close();
    }

    public ResultSet consulta(String sql) {
        ResultSet rs = null;
        try {
            Statement stmt = this.conn.createStatement();

            rs = stmt.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            setObj(ex);
        }

        return rs;
    }

    public boolean ejecuta(String sql) {
        boolean tf = false;
        try {
            Statement stmt = this.conn.createStatement();

            stmt.execute(sql);
            stmt.close();
            tf = true;
            return tf;
        } catch (SQLException ex) {
            tf = false;
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            setObj(ex);
//            throw ex;
        } finally {
            if (!tf) {
                System.err.println(sql);
            }
        }
        return tf;
    }

    public boolean startTransaction() {
        String sql = "START TRANSACTION";
        boolean tf = false;
        try {
            Statement stmt = this.conn.createStatement();

            stmt.execute(sql);
            stmt.close();
            tf = true;
            return tf;
        } catch (SQLException ex) {
            tf = false;
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
//            throw ex;
        } finally {
            if (!tf) {
                System.out.println(sql);
            }
        }
        return tf;
    }

    public boolean commit() {
        String sql = "COMMIT";
        boolean tf = false;
        try {
            Statement stmt = this.conn.createStatement();

            stmt.execute(sql);
            stmt.close();
            tf = true;
            return tf;
        } catch (SQLException ex) {
            tf = false;
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
//            throw ex;
        } finally {
            if (!tf) {
                System.out.println(sql);
            }
        }
        return tf;
    }

    public boolean update(String sql) {
        boolean tf = false;
        try {
            Statement stmt = this.conn.createStatement();

            stmt.executeUpdate(sql);
            stmt.close();
            tf = true;
            return tf;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            setObj(ex);
            tf = false;
            return tf;
        } finally {
            if (!tf) {
                System.out.println(sql);
            }
        }
//        return tf;
    }

    public Object getObj() {
        return this.obj;
    }

    public void setObj(Object obj) {
        Object oldObj = this.obj;
        this.obj = obj;
        this.propertyChangeSupport.firePropertyChange("obj", oldObj, obj);
    }

    public void addPropertyChangeListener(PropertyChangeListener listener) {
        this.propertyChangeSupport.addPropertyChangeListener(listener);
    }

    public void removePropertyChangeListener(PropertyChangeListener listener) {
        this.propertyChangeSupport.removePropertyChangeListener(listener);
    }
}
